package com.slipper.common.excel;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

import javax.servlet.http.HttpServletResponse;

import com.slipper.common.exception.CommonException;
import com.slipper.common.util.Tool;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;

import com.slipper.common.util.DateUtil;

public class ExcelExport {
    /**
     * 导出excel
     */
    @SuppressWarnings("rawtypes")
    public static void export(HttpServletResponse response, List list,
            Map<String, String> confMap, String fileName) throws Exception {
        export(response, list, confMap, null, fileName);
    }

    /**
     * 导出excel
     */
    @SuppressWarnings("rawtypes")
    public static void export(HttpServletResponse response, List list,
            Map<String, String> confMap, Map<String, Map<String, String>> dicMap, String fileName) throws Exception {
        response.reset();
        if (fileName == null || fileName.trim().isEmpty()) {
            fileName = "default.xlsx"; // 定义保存的文件名字
        }
        Tool.beforeExport(response, fileName);
        try {
            OutputStream os = response.getOutputStream();
            Workbook workbook = new HSSFWorkbook();
            Sheet sheet = workbook.createSheet();

            // 标题样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐
            titleStyle.setLocked(true); // 样式锁定
            titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 11);
            titleFont.setBold(true);
            titleFont.setFontName("微软雅黑");
            titleStyle.setFont(titleFont);
            titleStyle.setWrapText(true); // 换行

            // 标题样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐
            Font cellFont = workbook.createFont();
            cellFont.setBold(false);
            cellFont.setFontName("微软雅黑");
            cellStyle.setFont(cellFont);

            CellStyle textStyle = workbook.createCellStyle();
            // 设置为文本格式，防止身份证号变成科学计数法
            DataFormat textFormat = workbook.createDataFormat();
            textStyle.setDataFormat(textFormat.getFormat("@"));

            CellStyle number0Style = workbook.createCellStyle();
            // 设置为文本格式，防止身份证号变成科学计数法
            DataFormat number0Format = workbook.createDataFormat();
            number0Style.setDataFormat(number0Format.getFormat("0"));

            CellStyle number2Style = workbook.createCellStyle();
            // 设置为文本格式，防止身份证号变成科学计数法
            DataFormat number2Format = workbook.createDataFormat();
            number2Style.setDataFormat(number2Format.getFormat("0.00"));

            // ##############打印头#############################
            Row row = sheet.createRow(0);
            int columnIndex = 0;
            for (String value : confMap.values()) {
                String title;
                int width = 0;
                if (value != null && value.indexOf("@@@") > 0) {
                    String[] values = value.split("@@@");
                    title = values[0];
                    try {
                        if (values[1] != null && !values[1].isEmpty()) {
                            width = Integer.parseInt(values[1]);
                        }
                    } catch (Exception e) {
                        throw new CommonException("数据转换出错！", e);
                    }
                    if (values.length >= 3) {
                        String cellFormat = values[2];
                        if ("text".equals(cellFormat)) { // 文本列
                            // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
                            sheet.setDefaultColumnStyle(columnIndex, textStyle);
                        } else if ("number0".equals(cellFormat)) { // 文本列
                            // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
                            sheet.setDefaultColumnStyle(columnIndex, number0Style);
                        } else if ("number2".equals(cellFormat)) { // 文本列
                            // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
                            sheet.setDefaultColumnStyle(columnIndex, number2Style);
                        }
                    }
                } else {
                    title = value;
                }

                Cell cell = row.createCell(columnIndex);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(title);
                if (width > 0) {
                    sheet.setColumnWidth(columnIndex, width * 255);
                }

                columnIndex++;
            }

            // ############# body#############################
            for (int rowIndex = 0; rowIndex < list.size(); rowIndex++) {
                row = sheet.createRow(rowIndex + 1);
                BeanWrapper wrapper = null;
                Object data = list.get(rowIndex);
                if (!(data instanceof Map)) {
                    wrapper = new BeanWrapperImpl(data);
                }

                columnIndex = 0;
                for (Map.Entry<String, String> entry : confMap.entrySet()) {
                    Object lvalue;

                    String[] arr = entry.getKey().split("#");
                    String entryKey = arr[0];
                    String param = null;
                    if (arr.length > 1) {
                        param = arr[1];
                    }

                    if ("index".equals(param)) {
                        lvalue = rowIndex + 1;
                    } else {
                        if (!(data instanceof Map)) {
                            lvalue = wrapper.getPropertyValue(entryKey);
                        } else {
                            lvalue = ((Map) data).get(entryKey);
                        }
                        if (lvalue == null || (lvalue instanceof String && lvalue.equals(""))) {
                            lvalue = "";
                        }
                        if (lvalue instanceof Double) {
                            DecimalFormat df = new DecimalFormat("#.#");
                            lvalue = df.format(lvalue);
                        }
                        if (lvalue instanceof Date) {
                            if (param == null || param.trim().isEmpty()) {
                                String value = DateUtil.format((Date) lvalue, "yyyy-MM-dd HH:mm:ss");
                                lvalue = value.replace(" 00:00:00", "");
                            } else {
                                lvalue = DateUtil.format((Date) lvalue, param);
                            }
                        }
                        if (param != null
                                && !param.isEmpty()
                                && dicMap != null
                                && dicMap.containsKey(param)) {
                            if (lvalue instanceof String && ((String) lvalue).indexOf(",") > 0) {
                                String[] keys = ((String) lvalue).split(",");
                                StringBuilder builder = new StringBuilder();
                                for (String key : keys) {
                                    if (key != null && !key.isEmpty()) {
                                        builder.append(dicMap.get(param).get(key.trim())).append(",");
                                    }
                                }
                                if (builder.length() > 0) {
                                    lvalue = builder.delete(builder.length() - 1, builder.length());
                                } else {
                                    lvalue = "";
                                }
                            } else {
                                lvalue = dicMap.get(param).get(String.valueOf(lvalue));
                                if (lvalue == null) {
                                    lvalue = "";
                                }
                            }
                        }
                    }

                    if (!"".equals(lvalue.toString())) {
                        Cell cell = row.createCell(columnIndex);
                        cell.setCellStyle(cellStyle);
                        if (lvalue instanceof Long) {
                            cell.setCellValue((Long) lvalue);
                        } else if (lvalue instanceof Integer) {
                            cell.setCellValue((Integer) lvalue);
                        } else if (lvalue instanceof BigDecimal) {
                            cell.setCellValue(((BigDecimal) lvalue).doubleValue());
                        } else {
                            cell.setCellValue(lvalue.toString());
                        }
                    }

                    columnIndex++;
                }
            }
            workbook.write(os);
            workbook.close();
        } catch (Exception e) {
            throw new Exception(e);
        }
    }
}
